package xyz.matrix.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mysql.cj.util.StringUtils;

import xyz.matrix.model.PageBean;
import xyz.matrix.model.Student;
import xyz.matrix.util.DateUtil;

public class StudentDao {

	public ResultSet studentList(Connection con, PageBean pageBean, Student student, String bbirthday, String ebirthday) throws SQLException {
		// 
		StringBuffer sb = new StringBuffer("SELECT * FROM t_student s, t_grade g WHERE s.gradeId=g.id");
		if (!StringUtils.isNullOrEmpty(student.getStuNo())) {
			sb.append(" and stuNo like '%"+student.getStuNo()+"%'");
		}
		if (!StringUtils.isNullOrEmpty(student.getStuName())) {
			sb.append(" and stuName like '%"+student.getStuName()+"%'");
		}
		if (!StringUtils.isNullOrEmpty(student.getSex())) {
			sb.append(" and sex = '"+student.getSex()+"'");
		}
		if (student.getGradeId()!=-1) {
			sb.append(" and gradeId = '"+student.getGradeId()+"'");
		}
		if (!StringUtils.isNullOrEmpty(bbirthday)) {
			sb.append(" and TO_DAYS(s.birthday)>=TO_DAYS('"+bbirthday+"')");
		}
		if (!StringUtils.isNullOrEmpty(ebirthday)) {
			sb.append(" and TO_DAYS(s.birthday)<=TO_DAYS('"+ebirthday+"')");
		}
		
		if (pageBean != null) {
			sb.append(" limit " + pageBean.getStart() + "," + pageBean.getRows());
		}
		PreparedStatement pstmt = con.prepareStatement(sb.toString());
		ResultSet rs = pstmt.executeQuery();
		return rs;
	}
	
	public int studentCount(Connection con, Student student, String bbirthday, String ebirthday) throws SQLException{
		StringBuffer sb = new StringBuffer("select count(*) as total from t_student s, t_grade g where s.gradeId=g.id");
		if (!StringUtils.isNullOrEmpty(student.getStuNo())) {
			sb.append(" and stuNo like '%"+student.getStuNo()+"%'");
		}
		if (!StringUtils.isNullOrEmpty(student.getStuName())) {
			sb.append(" and stuName like '%"+student.getStuName()+"%'");
		}
		if (!StringUtils.isNullOrEmpty(student.getSex())) {
			sb.append(" and sex = '"+student.getSex()+"'");
		}
		if (student.getGradeId()!=-1) {
			sb.append(" and gradeId = '"+student.getGradeId()+"'");
		}
		if (!StringUtils.isNullOrEmpty(bbirthday)) {
			sb.append(" and TO_DAYS(s.birthday)>=TO_DAYS('"+bbirthday+"')");
		}
		if (!StringUtils.isNullOrEmpty(ebirthday)) {
			sb.append(" and TO_DAYS(s.birthday)<=TO_DAYS('"+ebirthday+"')");
		}
		
		PreparedStatement pstmt = con.prepareStatement(sb.toString());
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			return rs.getInt("total");
		} else {
			return 0;
		}
	}
	public int deleteStudent(Connection con, String delIds) throws SQLException {
		String sql = "delete from t_student where stuId in ("+delIds+")";
		PreparedStatement pstmt = con.prepareStatement(sql);
		return pstmt.executeUpdate();
	}
	
	public int addStudent(Connection con, Student student) throws SQLException {
		String sql = "insert into t_student values(null,?,?,?,?,?,?,?)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, student.getStuNo());
		pstmt.setString(2, student.getStuName());
		pstmt.setString(3, student.getSex());
		pstmt.setString(4, DateUtil.formatDate(student.getBirthday(), "yyyy-MM-dd"));
		pstmt.setInt(5, student.getGradeId());
		pstmt.setString(6, student.getEmail());
		pstmt.setString(7, student.getStuDesc());
		return pstmt.executeUpdate();
		
	}
	
	public int updateStudent(Connection con, Student student) throws SQLException {
		String sql = "update t_student set stuNo=?,stuName=?,sex=?,birthday=?,gradeId=?,email=?,stuDesc=? where stuId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, student.getStuNo());
		pstmt.setString(2, student.getStuName());
		pstmt.setString(3, student.getSex());
		pstmt.setString(4, DateUtil.formatDate(student.getBirthday(), "yyyy-MM-dd"));
		pstmt.setInt(5, student.getGradeId());
		pstmt.setString(6, student.getEmail());
		pstmt.setString(7, student.getStuDesc());
		pstmt.setInt(8, student.getStuId());
		return pstmt.executeUpdate();
	}
	public boolean getStudentByGradeId(Connection con, String gradeId) throws SQLException {
		String sql = "select * from t_student where gradeId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, Integer.parseInt(gradeId));
		
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			return true;
		} else {
			return false;
		}
	
	}
}
